﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jamaat_Financial_System.Model.BO;
using System.Collections.ObjectModel;
using System.Data.OleDb;
using Jamaat_Financial_System.Model.Util;

namespace Jamaat_Financial_System.Model.DAO
{
    class Receipt_DAO : Base_DAO
    {
        public ObservableCollection<Model.BO.Base_BO> _ReceiptList = new ObservableCollection<Model.BO.Base_BO>();

        public override ObservableCollection<Model.BO.Base_BO> UpdateList()
        {
            command = new OleDbCommand("SELECT BookNo FROM "+Constants.TableNames.BOOKNO+" WHERE Status='Active' ORDER BY BookNo", connection);
            OleDbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Model.BO.Receipt_BO ReceiptBO = new Model.BO.Receipt_BO();
                ReceiptBO.BookNo = reader.GetString(Constants.BookTble.BOOKNO);
                _ReceiptList.Add(ReceiptBO);
            }

            return _ReceiptList;
        }

        public override int Insert(Model.BO.Base_BO _record)
        {
            try
            {
                Model.BO.Receipt_BO record = (Model.BO.Receipt_BO)_record;
                String BookNo = record.BookNo;

                command = new OleDbCommand("SELECT * FROM TblBookNo WHERE BookNo = '" + BookNo + "'", connection);
                OleDbDataReader reader = command.ExecuteReader();
                if (!reader.Read())
                {
                    command = new OleDbCommand("INSERT INTO TblBookNo VALUES('" + BookNo + "', 'Active')", connection);

                    if (command.ExecuteNonQuery() == 1)
                    {
                        int TotalInserted = 0;
                        transaction = connection.BeginTransaction();

                        for (int i = 1; i <= 100; i++)
                        {
                            String ReceiptNo = i.ToString().PadLeft(3, '0');
                            command = new OleDbCommand("INSERT INTO TblRecieptNo VALUES('" + ReceiptNo + "','" + BookNo + "', 'Active')", connection, transaction);
                            TotalInserted += command.ExecuteNonQuery();

                        }

                        if (TotalInserted == 100)
                        {
                            transaction.Commit();
                            _ReceiptList.Add(record);
                        }
                        else
                        {
                            command.Transaction.Rollback();
                            return ErrorCodes_BO.FAILED_TO_INSERT_RECORD;
                        }
                    }
                }
                else
                {
                    command = new OleDbCommand("UPDATE TblBookNo SET Status='Active' WHERE BookNo ='" + BookNo + "'", connection);
                    if (command.ExecuteNonQuery() == 1)
                    {
                        _ReceiptList.Add(record);
                        return ErrorCodes_BO.SUCCESS;
                    }
                    else
                    {
                        return ErrorCodes_BO.FAILED_TO_INSERT_RECORD;
                    }
                }

                return ErrorCodes_BO.SUCCESS;

            }
            catch (Exception ex)
            {
                return ErrorCodes_BO.FAILED_TO_INSERT_RECORD;
            }
        }

        public override int Delete(Model.BO.Base_BO _record)
        {
            try
            {
                Model.BO.Receipt_BO record = (Model.BO.Receipt_BO)_record;
                String BookNo = record.BookNo;

                command = new OleDbCommand("UPDATE TblBookNo SET Status='Deleted' WHERE BookNo ='" + BookNo + "'", connection);
                if (command.ExecuteNonQuery() == 1)
                {
                    _ReceiptList.Clear();
                    return ErrorCodes_BO.SUCCESS;
                }
                else
                {
                    return ErrorCodes_BO.FAILED_TO_DELETE_RECORD;
                }
            }
            catch (Exception ex)
            {
                return ErrorCodes_BO.FAILED_TO_DELETE_RECORD;
            }
        }    
    }
}
